from crawler import collector
from core.database_help import DataBaseCall
from core.data_help import TextFilter, load_stop_words, SentenceCut,VegDB
from predict_lstm import LstmPredictModel
from core.causality_extract import CausalityExractor

dbc = DataBaseCall()
dbc.connect_mysql()
dbc.connect_neo4j()

clc = collector()




def addCol(df,name):
    if name not in df.columns:
        df.insert(len(df.columns),name,'')



def TextAnalyze(df,lpm,tf,ce):


    # 文本筛选
    addCol(df,'Veg')
    addCol(df,'City')
    df = tf.filterDataFrame(df,'Text')
    df = tf.vegCityFilterDataFrame(df)


    # 文本分类
    addCol(df,'PriceSenti')
    addCol(df,'Indicator')
    lpm.load_model('all')
    df = lpm.SentiPredict(df)
    df = lpm.IndPredict(df)


    addCol(df,'CauseEvent')
    addCol(df,'ResultEvent')
    addCol(df,'CauseTag')
    # 因果事件抽取
    for i in df.index:
        datas = ce.extract_main(df.loc[i]['Text'])
        CauseEventList = []
        ResultEventList = []
        TagList = []
        for data in datas:
            CauseEventList.append(''.join([word.split('/')[0] for word in data['cause'].split(' ') if word.split('/')[0]]))
            ResultEventList.append(''.join([word.split('/')[0] for word in data['effect'].split(' ') if word.split('/')[0]]))
            TagList.append(data['tag'])
        if CauseEventList and ResultEventList:
            df.loc[i,'CauseEvent'] = '@'.join(CauseEventList)
            df.loc[i,'ResultEvent'] = '@'.join(ResultEventList)
            df.loc[i,'CauseTag'] = '@'.join(TagList)
    return df

def getFromSQL():
    return dbc.readFromSQL('select * from article')

# df = getFromSQL()
def articleAnalyze(df):
    sc = SentenceCut(load_stop_words())
    tf = TextFilter(sc)
    vegdb = VegDB(sc,'model/vocab')
    lpm = LstmPredictModel(vegdb)
    ce = CausalityExractor()

    df = TextAnalyze(df, lpm, tf, ce)
    # print(df)
    dbc.writeToMySQL(df,'article','replace')
    # print("OK")
    df = dbc.readFromSQL("select * from article")
    # df = clc.GetPrice()
    # df.to_excel('ArticleNew1.xlsx')
    # print(df.head())
    for i in df.index:
        item = df.loc[i]
        cql = '''match (p:Vegetable {name:'%s'})\
        match (q:City) where q.name='%s' or q.shortName='%s'\
        merge (p)<-[r:RelatedVegetable]-(n:Article {aid:%d, title:'%s', date:'%s', text:'%s', city:'%s', veg:'%s', priceSenti:'%s', indicator:'%s', url:'%s'})-[r2:RelatedCity]->(q)\
        '''%(item['Veg'],item['City'],item['City'],i,item['Title'],item['Date'],item['Text'],item['City'],item['Veg'],item['PriceSenti'],item['Indicator'],item['Url'])
        dbc.neo4j.run(cql)
        # print(cql)
        # with n create (a:Event {type:'Cause'})-[r:RelatedArticle]->(n)<-[r:RelatedArticle]-(b {type:'Result'})\
        # with a,b create (a)-[r:Cause]->(b)\
        if item['CauseEvent'] and item['ResultEvent']:
            ces = item['CauseEvent'].split('@')
            res = item['ResultEvent'].split('@')
            for ce,re in zip(ces,res):  
                cql = '''match (n:Article{aid:%d}) merge (a:Event {name: '%s',type:'Cause'})-[:RelatedArticle]->(n)<-[:RelatedArticle]-(b:Event{name: '%s',type:'Result'})'''%(i,ce,re)
                dbc.neo4j.run(cql)
                # print(cql)
                cql = '''match (a:Event {name: '%s'}) match (b:Event {name: '%s'}) merge (a)-[r:Cause]->(b)'''%(ce, re)
                dbc.neo4j.run(cql)
                # print(cql)

        # for j in range(len(ces)):
        #     cql = '''match (n:Article{aid:%d}) merge (a:Event {name: '%s',type:'Cause'})-[:RelatedArticle]->(n)<-[:RelatedArticle]-(b:Event{name: '%s',type:'Result'})'''%(j,ces[j],res[j])
        #     dbc.neo4j.run(cql)
        #     print(cql)
        #     cql = '''match (a:Event {name: '%s'}) match (b:Event {name: '%s'}) merge (a)-[r:Cause]->(b)'''%(ces[j], res[j])
        #     dbc.neo4j.run(cql)
        #     print(cql)
def getArticleFromNetWork(latest_url,page=50):
    urlList = clc.getArticleList(latest_url,page)
    df = clc.GetArticle(urlList,page*10)
    # dbc.writeToMySQL(df,'article','replace')
    return df


def getPriceFromNetWork(latest_url,page=50):
   
    df = clc.GetPrice(latest_url,page)
    dbc.writeToMySQL(df,'price','append')
    return df


if __name__ == "__main__":
    # getPrice(50)
    price_latest_url = article_latest_url = ''
    if not price_latest_url and 'price' in dbc.mysql.table_names():
        price_latest = dbc.mysql.execute('select * from price order by Url desc limit 1').fetchone()
        price_latest_url = price_latest[price_latest.keys().index('Url')]
        print(price_latest)
        print(price_latest[price_latest.keys().index('Date')], price_latest_url)
    if not article_latest_url and 'article' in dbc.mysql.table_names():
        article_latest = dbc.mysql.execute('select * from article order by Url desc limit 1').fetchone()
        article_latest_url = article_latest[article_latest.keys().index('Url')]
        print(article_latest)
        print(article_latest[article_latest.keys().index('Date')], article_latest_url)
    
    df2 = getPriceFromNetWork(price_latest_url,6)
    df1 = getArticleFromNetWork(article_latest_url,6)
    
    articleAnalyze(df1)
    df2.to_excel('PriceDemo.xlsx')
    df1.to_excel('ArticleDemo.xlsx')
